# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: https://docs.scrapy.org/en/latest/topics/item-pipeline.html


# useful for handling different item types with a single interface
from itemadapter import ItemAdapter
import pymysql


class Spider51JobPipeline:
    def process_item(self, item, spider):
        print(item)
        return item


class Spider51JobMysqlPipeline:
    def open_spider(self, spider):
        # 读取settings.py中的配置项
        host = spider.settings.get("MYSQL_DB_HOST")
        port = spider.settings.get("MYSQL_DB_PORT")
        dbname = spider.settings.get("MYSQL_DB_NAME")
        user = spider.settings.get("MYSQL_DB_USER")
        pwd = spider.settings.get("MYSQL_DB_PASSWORD")
        # 创建数据库链接
        self.db_conn = pymysql.connect(host=host, port=port, db=dbname, user=user, password=pwd)
        # 打开游标
        self.db_cur = self.db_conn.cursor()

    def process_item(self, item, spider):
        job_table_name = spider.settings.get("MYSQL_DB_JOB_TABLE_NAME")
        # print(dict(it，这调整一下。
        values = (
            item["title"],
            item['company'],
            item["salary"],
            item['address'],
            item["post"],
            item['experience'],
            item['message'],

        )  # 与占位符%s对应的数据
        # sql语句，数据部分使用占位符%s代替
        sql = "insert into "+ job_table_name +"(job_title,job_company,job_salary,job_address,job_post,job_experience,job_message) values(%s,%s,%s,%s,%s,%s,%s)"
        self.db_cur.execute(sql, values)  # 执行SQL语句
        self.db_conn.commit()
        return item

    def close_spider(self, spider):
        self.db_cur.close()  # 关闭游标
        self.db_conn.close()  # 关闭数据库连接
